﻿using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TableToEntity
{
    /// <summary>
    /// 生成MsSql数据库实体逻辑
    /// </summary>
    public static class CreatePostgreSqlModel
    {
        #region 获取基本信息

        private static readonly string infoSql = @"select info.*,
                                                    col.column_default  devalue from (
                                                    SELECT 
		                                                        c.relname  tbname,
		                                                        (case when a.attname = 'gCode' or a.attname = 'Id' or a.attname = 'ID' or a.attname = 'id'  then 'true' else 'false' end) isPrimarykey,
			                                                    des.description tabDescribe,
			                                                    a.attname AS colName,
			                                                    t.typname AS dbType,
			                                                    a.attlen AS dbLength,
			                                                    a.atttypmod AS byteLength,
			                                                    0 decimalDigits,
			                                                    a.attnotnull AS required,
			                                                    b.description AS colDescribe
			                                                    FROM pg_class c LEFT OUTER JOIN 
			                                                    (
			                                                    with tmp_tab as (
					                                                    select pc.oid as ooid,pc.*
						                                                    from pg_class pc
						                                                    where 1=1
						                                                        and pc.relkind in ('r','v','m','f','p')
					                                                    order by pc.relname
			                                                    ),
			                                                    tmp_desc as (
				                                                        select pd.*
					                                                        from pg_description pd
					                                                    where 1=1
						                                                    and pd.objsubid = 0 --objsubid 对于一个表列上的一个注释，这里是列号（objoid和classoid指表本身）。对所有其他对象类型，此列为0。
						                                                    --and pd.objoid=168605
			                                                    )
			                                                    select tab.relname,
				                                                        de.description
			                                                        from tmp_tab tab
					                                                    left outer join tmp_desc de
			                                                        on tab.ooid = de.objoid 
			                                                    ) des 
			                                                    ON c.relname=des.relname
			                                                    ,pg_attribute a
			                                                    LEFT OUTER JOIN pg_description b 
			                                                    ON a.attrelid=b.objoid AND a.attnum = b.objsubid, 
			                                                    pg_type t
			                                                    WHERE c.relname = '{0}'
			                                                    and a.attnum > 0
			                                                    and a.attrelid = c.oid
			                                                    and a.atttypid = t.oid
			                                                    --ORDER BY a.attnum
                                                    ) info
                                                    left join  information_schema.columns col 
                                                    on info.tbname=col.table_name and info.colName=col.column_name";

        private static readonly string VIEWS_LIST_SQL = @"SELECT   viewname as name  FROM   pg_views  WHERE   schemaname ='{0}'  ";


        private static readonly string VIEWS_INFO_SQL = @"SELECT '{0}' tbname,
                                                        a.attname AS colName,
                                                        t.typname AS dbType
                                                        FROM pg_class c,
                                                        pg_attribute a LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
                                                        pg_type t 
                                                        WHERE c.relname = '{0}' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY a.attnum;";



        #endregion


        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="address">链接地址</param>
        /// <param name="dbName">数据库名称</param>
        /// <param name="account">账号</param>
        /// <param name="password">密码</param>
        /// <returns></returns>
        private static string GetConnectionStr(DBInfo dbInfo)
        {
            string conStr = $"HOST={dbInfo.Service};PORT={dbInfo.Port};DATABASE={dbInfo.DbName};USER ID={dbInfo.Account};PASSWORD={dbInfo.Password}";
            return conStr;
        }

        /// <summary>
        /// 获取数据库中的表
        /// </summary>
        /// <param name="type">0表 1视图</param>
        /// <returns></returns>
        public static  string[] GetTableNames(int type, DBInfo dbInfo, ModelInfo modelInfo,string schema= "public")
        {
            string[] resultList = null;
            NpgsqlConnection conn = null;
            string conStr = GetConnectionStr(dbInfo);
            try
            {
                conn = new NpgsqlConnection(conStr);
                conn.Open();

                if (type == 0)
                {
                    string[] restrictions = new string[4];
                    List<string> tabNames = new List<string>();
                    restrictions[0] = modelInfo.ModelNameSpace;
                    DataTable table = conn.GetSchema("Tables");

                    conn.Close();

                    foreach (DataRow row in table.Rows)
                    {
                        if (row["table_schema"].ToString()== schema)
                        {
                            tabNames.Add(row.ItemArray[2].ToString());
                        }
                    }
                    resultList= tabNames.ToArray<string>();
                }
                else
                {
                    List<string> tabNames = new List<string>();
                    using (NpgsqlCommand command = new NpgsqlCommand(string.Format(VIEWS_LIST_SQL, schema)  , conn))
                    {
                        var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                        while (reader.Read())
                        {
                            tabNames.Add(reader["name"].ToString());
                        }
                        reader.Close();
                    }
                    resultList= tabNames.ToArray();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return resultList;
        }

        /// <summary>
        /// 获取数据库中的表信息
        /// </summary>
        /// <param name="tbnames"></param>
        /// <param name="dbInfo"></param>
        /// <returns></returns>
        public static Dictionary<string, List<FiledInfo>> GetTableInfo(string[] tbnames, DBInfo dbInfo)
        {
            var ret = new Dictionary<string, List<FiledInfo>>();
            NpgsqlConnection conn = null;
            string conStr = GetConnectionStr(dbInfo);

            try
            {
                conn = new NpgsqlConnection(conStr);
                conn.Open();

                foreach (var item in tbnames)
                {
                    string sql = string.Format(infoSql, item);
                    NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
                    List<FiledInfo> filed = new List<FiledInfo>();
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            FiledInfo fi = new FiledInfo();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                fi.isPrimarykey = reader["isPrimarykey"].ToString();
                                fi.colName = reader["colName"].ToString();
                                fi.dbType = reader["dbType"].ToString();
                                fi.colDescribe = reader["colDescribe"].ToString();
                                fi.devalue = reader["devalue"].ToString();
                                fi.required = reader["required"].ToString();
                                fi.tabDescribe = reader["tabDescribe"].ToString();
                            }
                            filed.Add(fi);
                        }
                    }
                    ret.Add(item, filed);
                }
              
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return ret;

        }


        /// <summary>
        /// 获取数据库中的视图信息
        /// </summary>
        /// <param name="viewNames"></param>
        /// <param name="dbInfo"></param>
        /// <returns></returns>
        public static Dictionary<string, List<FiledInfo>> GetViewInfo(string[] viewNames, DBInfo dbInfo)
        {
            Dictionary<string, List<FiledInfo>> ret = new Dictionary<string, List<FiledInfo>>();
            NpgsqlConnection conn = null;
            string conStr = GetConnectionStr(dbInfo);

            try
            {
                conn = new NpgsqlConnection(conStr);

                foreach (var item in viewNames)
                {
                    conn.Open();
                    string sql = string.Format(VIEWS_INFO_SQL, item);
                    using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
                    {
                        List<FiledInfo> filed = new List<FiledInfo>();
                        using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (reader.Read())
                            {
                                FiledInfo fi = new FiledInfo();
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    fi.colName = reader["colName"].ToString();
                                    fi.dbType = reader["dbType"].ToString();
                                }
                                filed.Add(fi);
                            }
                        }
                        ret.Add(item, filed);
                    }
                }
                return ret;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
    }
}
